#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#
# Copyright [Gtlions Lai].
# Create Date:
# Update Date:
"""summarization ahout this script.

detail ahout this script

   Class(): summarization about Class
   ...
   function(): summarization about function
   ...
"""
__authors__ = '"Gtlions Lai" <gtlions.l@qq.com>'

import re
import time
import datetime
import sys

from PyQt4 import QtCore, QtGui
import win32com.client
import pythoncom

import kexshop.gtcom.commod


class GpdbGenDdlFromWordGui(QtGui.QWidget):
    def __init__(self):
        QtGui.QWidget.__init__(self)
        self.setWindowTitle(u"GPDB DDL 生成器")
        self.resize(500, 500)

        self.mdiArea = QtGui.QMdiArea(self)
        self.mdiArea.setGeometry(QtCore.QRect(0, 0, 500, 500))
        self.mdiArea.setViewMode(QtGui.QMdiArea.TabbedView)
        self.mdiArea.setObjectName("mdiArea")

        self.subwindow1 = QtGui.QWidget()
        self.subwindow1.setObjectName("subwindow1")
        self.subwindow1.setWindowTitle(u"生成DDL")
        grid = QtGui.QGridLayout(self.subwindow1)
        # 8 x 10
        grid.setHorizontalSpacing(8)
        grid.setVerticalSpacing(10)

        self.btchoicefile = QtGui.QPushButton(u'选择文件')
        self.qlfilename = QtGui.QLineEdit(u'...... 请选择MS-WORD文件 ......')
        self.qlfilename.setAlignment(QtCore.Qt.AlignCenter)
        self.qlfilename.setEnabled(False)
        self.qprocess = QtGui.QProgressBar()

        self.execret = QtGui.QTextBrowser()
        self.execret.insertPlainText(u'执行结果...... \n')
        grid.addWidget(self.qlfilename, 0, 0, 1, 9)
        grid.addWidget(self.btchoicefile, 0, 9, 1, 1)
        grid.addWidget(self.qprocess, 1, 0, 1, 10)

        grid.addWidget(self.execret, 2, 0, 5, 10)

        self.subwindow2 = QtGui.QWidget()
        self.subwindow2.setObjectName("subwindow2")
        self.subwindow2.setWindowTitle(u"说明")
        grid2 = QtGui.QGridLayout(self.subwindow2)
        te1 = QtGui.QTextEdit('')
        te1.setText(u"""用于生成GPDB的DDL文件<br>
        文档格式必须严格按照模板编写<br>
        支持从MS-WORD文档中直接解析，文档中可以有多个表的信息<br>
        注意：MS-WORD文档文件名不能空格<br>
        By 赖东叶""")
        te1.setAlignment(QtCore.Qt.AlignCenter)
        te1.setEnabled(False)
        grid2.addWidget(te1)

        self.mdiArea.addSubWindow(self.subwindow2)
        self.mdiArea.addSubWindow(self.subwindow1)
        QtCore.QObject.connect(self.btchoicefile, QtCore.SIGNAL("clicked()"), self.choicefile)
        # QtCore.QObject.connect(self,QtCore.SIGNAL("filenameerror"), self.filenameerror)

    def update_execret(self, message):
        self.execret.insertPlainText(message)

    # def filenameerror(self):
    #     self.execret.insertPlainText('as')
    def finished(self):
        self.btchoicefile.setEnabled(True)

    def choicefile(self):
        filename = unicode(QtGui.QFileDialog.getOpenFileName(self, caption=u"请选择一个Word文件", directory=".",
                                                             filter="MS-WORD (*.doc *.docx);;ALL files (*.*)"))
        # text = codecs.open(filePath, "r", "mbcs").read().encode("utf-8")
        if filename:
            self.execret.clear()
            self.execret.insertPlainText(u'执行结果...... \n')
            self.qlfilename.setText(filename)
            self.qlfilename.setAlignment(QtCore.Qt.AlignCenter)
            try:
                thread = GenDdl(self)  # 创建线程
                thread.trigger.connect(self.update_execret)  # 连接信号！
                thread.setup(filename)  # 传递参数
                thread.start()  # 启动线程
                self.btchoicefile.setEnabled(False)
                self.connect(thread, QtCore.SIGNAL("finished()"), self.finished)
            except Exception, e:
                print 'a'
        else:
            self.qlfilename.setText(u'没有选择任何文件')
            self.qlfilename.setAlignment(QtCore.Qt.AlignCenter)
        self.qprocess.setMinimum(0)
        self.qprocess.setMaximum(10)


class GenDdl(QtCore.QThread):
    trigger = QtCore.pyqtSignal(str)  # trigger传输的内容是字符串

    def __init__(self, parent=None):
        super(GenDdl, self).__init__(parent)

    def removechars(self, cellvalue):
        text = re.sub(r"[\r\n\t\x07\x0b]", "", cellvalue)
        return text

    def setup(self, args):
        self.args = args

    def run(self):  # 很多时候都必重写run方法, 线程start后自动运行
        self.my_function()

    def my_function(self):
        try:
            pythoncom.CoInitialize()
            app = win32com.client.Dispatch('Word.Application')
            doc = app.Documents.Open(FileName=self.args)
        except Exception, e:
            self.trigger.emit(u' 出错了，请确保MS-WORD文件名称没有包含空格以及其他未知字符，请检查文件名称或者修改文件名称为英文字母\n')
            # self.trigger.emit(QtCore.SIGNAL("filenameerror"))
            return
        ddl_sql_file_name = 'GPDB_GEN_DDL_FROM_WORD.sql'
        ddl_sql_file = open(ddl_sql_file_name, 'w')
        ddl_sql_file.truncate()
        ddl_sql_file.write("-- Generate GreenPlum DDL SQL From MS-WORD --\n")
        ddl_sql_file.close()
        self.trigger.emit(u' 一共有[' + str(doc.Tables.count) + u']张表. \n')

        for tabcounts in range(doc.Tables.count):
            wordtable = doc.Tables(tabcounts + 1)

            database = self.removechars(wordtable.Cell(1, 2).Range.Text)
            owner = self.removechars(wordtable.Cell(1, 4).Range.Text)
            schema = self.removechars(wordtable.Cell(1, 6).Range.Text)
            table = self.removechars(wordtable.Cell(2, 2).Range.Text)
            self.trigger.emit(' ### ' + str(tabcounts + 1) + u' . 正在解析表[' + table + ']. \n')
            table_commnet = self.removechars(wordtable.Cell(2, 4).Range.Text)
            status = self.removechars(wordtable.Cell(2, 6).Range.Text)

            distributed_type = self.removechars(wordtable.Cell(3, 2).Range.Text)
            distributed_key = self.removechars(wordtable.Cell(3, 4).Range.Text)
            appendonly = self.removechars(wordtable.Cell(3, 6).Range.Text)

            orientation = self.removechars(wordtable.Cell(4, 2).Range.Text)
            compresstype = self.removechars(wordtable.Cell(4, 4).Range.Text)
            compresslevel = self.removechars(wordtable.Cell(4, 6).Range.Text)

            one_part = self.removechars(wordtable.Cell(5, 2).Range.Text)
            one_part_type = self.removechars(wordtable.Cell(5, 4).Range.Text)
            one_part_key = self.removechars(wordtable.Cell(5, 6).Range.Text)
            one_part_format = self.removechars(wordtable.Cell(6, 2).Range.Text)
            one_part_start = self.removechars(wordtable.Cell(6, 4).Range.Text)
            one_part_end = self.removechars(wordtable.Cell(6, 6).Range.Text)
            two_part = self.removechars(wordtable.Cell(7, 2).Range.Text)
            two_part_type = self.removechars(wordtable.Cell(7, 4).Range.Text)
            two_part_key = self.removechars(wordtable.Cell(7, 6).Range.Text)
            two_part_format = self.removechars(wordtable.Cell(8, 2).Range.Text)
            two_part_start = self.removechars(wordtable.Cell(8, 4).Range.Text)
            two_part_end = self.removechars(wordtable.Cell(8, 6).Range.Text)
            # print database
            # print owner
            # print schema
            # print table
            # print table_commnet
            # print status
            # print distributed_type
            # print distributed_key
            # print appendonly
            # print orientation
            # print compresstype
            # print compresslevel
            # print one_part
            # print one_part_type
            # print one_part_key
            # print one_part_format
            # print one_part_start
            # print one_part_end
            # print two_part
            # print two_part_type
            # print two_part_key
            # print two_part_format
            # print two_part_start
            # print two_part_end
            start_commnet = '\n----------' + str(tabcounts + 1) + ' ' + table + '----------\n'
            ddl_commnet = '\n-- Create table ' + schema + '.' + table + ' On Database[' + database + '] Owner [' + owner + '].\n--Drop table if exists ' + schema + '.' + table + ';\n'
            table_header_sql = '\nCreate table ' + schema + '.' + table + '\n'
            column_list_sql = ''
            index_sql = ''
            column_commnet_sql = ''
            with_sql = ''
            distributed_sql = ''
            one_part_sql = ''
            one_part_lsit_sql = ''
            table_owner_sql = ''
            table_commnet_sql = ''
            one_part_key_type = ''
            for r in range(10, wordtable.Rows.Count):
                # column_list_sql
                column_name = self.removechars(wordtable.Cell(r + 1, 1).Range.Text)
                column_type = self.removechars(wordtable.Cell(r + 1, 2).Range.Text)
                if one_part_key == column_name:
                    one_part_key_type = column_type
                    # print one_part_key, one_part_key_type,column_type
                column_list_sql += column_name + ' ' + column_type
                if self.removechars(wordtable.Cell(r + 1, 3).Range.Text).upper() == 'N':
                    column_list_sql += ' not null'
                if self.removechars(wordtable.Cell(r + 1, 4).Range.Text).upper() == 'Y':
                    column_list_sql += ' primary key'
                if r == wordtable.Rows.Count - 1:
                    pass
                else:
                    column_list_sql += ',\n'

                # index_sql
                if self.removechars(wordtable.Cell(r + 1, 5).Range.Text).upper() == 'Y':
                    index_sql += 'create index idx_' + table + '_' + column_name + ' on ' + schema + '.' + table + '(' + column_name + ');\n'
                # column_commnet_sql
                column_commnet_text = self.removechars(wordtable.Cell(r + 1, 6).Range.Text)
                if self.removechars(wordtable.Cell(r + 1, 6).Range.Text):
                    column_commnet_sql += "comment on column " + schema + '.' + table + '.' + column_name + " is '" + column_commnet_text + "';\n"
            column_list_sql = '(\n' + column_list_sql + '\n)\n'
            # print column_list_sql
            if appendonly.upper() == 'Y':
                with_sql = 'with (appendonly=true,orientation=' + orientation
                if compresstype.upper() == 'ZLIB':
                    with_sql += ',compresstype=zlib,compresslevel=' + compresslevel
                elif compresstype.upper() == 'QUICKLZ':
                    with_sql += ',compresstype=quicklz'
                with_sql += ')'

            if distributed_type.upper() == 'HASH':
                distributed_sql = '\ndistributed by (' + distributed_key + ')';
            else:
                distributed_sql = '\ndistributed randomly'

            # partitions by one level
            if one_part.upper() == 'Y':
                distributed_sql += '\n';
                one_part_sql = 'partition by ' + one_part_type + '(' + one_part_key + ')\n'

                if one_part_format.upper() == 'YYYYMMDD':
                    y, m, d = time.strptime(one_part_start, '%Y%m%d')[0:3]
                    o_d1_d = datetime.date(y, m, d)
                    o_d1 = datetime.date(y, m, d).strftime('%Y%m%d')

                    y, m, d = time.strptime(one_part_end, '%Y%m%d')[0:3]
                    o_d2_d = datetime.date(y, m, d)
                    o_d2 = datetime.date(y, m, d).strftime('%Y%m%d')
                    for i in range((o_d2_d - o_d1_d).days):
                        start = (o_d1_d + datetime.timedelta(days=i)).strftime('%Y%m%d')
                        end = (o_d1_d + datetime.timedelta(days=i + 1)).strftime('%Y%m%d')
                        if one_part_key_type in ['timestamp']:
                            one_part_lsit_sql += "partition " + one_part_key + start + " start('" + start + "'::date) end ('" + end + "'::date) " + with_sql
                        else:
                            one_part_lsit_sql += 'partition ' + one_part_key + start + ' start(' + start + ') end (' + end + ') ' + with_sql
                        if i < (o_d2_d - o_d1_d).days - 1:
                            one_part_lsit_sql += ',\n'
                    one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
                elif one_part_format.upper() == 'YYYYMM':
                    one_part_start = str(int(one_part_start))
                    one_part_end = str(int(one_part_end))
                    mondict = kexshop.gtcom.commod.GetMonDict(one_part_start, one_part_end)
                    for start, end in [(k, mondict[k]) for k in sorted(mondict.keys())]:
                        one_part_lsit_sql += 'partition ' + one_part_key + start + ' start(' + start + ') end (' + end + ') ' + with_sql
                        if start != one_part_end:
                            one_part_lsit_sql += ',\n'
                    one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
                elif one_part_format.upper() == 'MM':
                    for i in range(12):
                        start = i + 1
                        end = start + 1
                        one_part_lsit_sql += 'partition ' + one_part_key + str(start) + ' start(' + str(
                            start) + ') end (' + str(end) + ') ' + with_sql
                        if start < 12:
                            one_part_lsit_sql += ',\n'
                    one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
            else:
                distributed_sql += ';\n'

            ddl_sql_file = open(ddl_sql_file_name, 'a')
            ddl_sql_file.write(
                start_commnet.encode('utf8') + ddl_commnet.encode('utf8') + table_header_sql.encode(
                    'utf8') + column_list_sql.encode('utf8') + with_sql.encode(
                    'utf8') + distributed_sql.encode('utf8') + one_part_sql.encode('utf8') + one_part_lsit_sql.encode(
                    'utf8'))
            # print ddl_commnet + table_header+ column + with_sql + distributed_sql + one_part_sql + one_part_lsit_sql

            index_commnet = '\n-- Create Index\n'
            ddl_sql_file.write(index_commnet.encode('utf8') + index_sql.encode('utf8'))
            # print index_commnet + index

            table_commnet_comment = '\n-- Table comment\n'
            if table_commnet:
                table_commnet_sql = "comment on table " + schema + '.' + table + " is '" + table_commnet + "';"
            ddl_sql_file.write(table_commnet_comment + table_commnet_sql.encode('utf8'))
            # print  table_commnet_comment + table_commnet_sql

            column_commnet_commnet = '\n-- Column comment\n'
            ddl_sql_file.write(column_commnet_commnet + column_commnet_sql.encode('utf8'))
            # print  column_commnet_commnet + column_commnet

            table_owner_commnet = '\n-- Table owner\n'
            table_owner_sql = 'alter table ' + schema + '.' + table + ' owner to ' + owner + ';\n'
            ddl_sql_file.write(table_owner_commnet.encode('utf8') + table_owner_sql.encode('utf8'))
            # print table_owner_commnet + table_owner_sql
            ddl_sql_file.close()
        self.trigger.emit(u'执行完成...... \n')
        self.trigger.emit(u'解析结果保存在文件[' + ddl_sql_file_name + u']中 \n')


if __name__ == "__main__":
    mainapp = QtGui.QApplication(sys.argv)
    myapp = GpdbGenDdlFromWordGui()
    myapp.show()
    sys.exit(mainapp.exec_())
